{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data={\n",
    "    \"boss\":[\"A\"]*3+[\"B\"]*3+[\"C\"]*4,\n",
    "    \"owner\":[\"A1\",\"A1\",\"A2\",\"B1\",\"B2\",\"B2\",\"C1\",\"C1\",\"C2\",\"C2\"],\n",
    "    \"month\":[1,2,1,1,1,2,1,2,1,2],\n",
    "    \"fk_money\":[10,20,30,40,50,60,70,80,90,100],\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>boss</th>\n",
       "      <th>owner</th>\n",
       "      <th>month</th>\n",
       "      <th>fk_money</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A2</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>B1</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>2</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  boss owner  month  fk_money\n",
       "0    A    A1      1        10\n",
       "1    A    A1      2        20\n",
       "2    A    A2      1        30\n",
       "3    B    B1      1        40\n",
       "4    B    B2      1        50\n",
       "5    B    B2      2        60\n",
       "6    C    C1      1        70\n",
       "7    C    C1      2        80\n",
       "8    C    C2      1        90\n",
       "9    C    C2      2       100"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据解释：\n",
    "比如第一条数据，老板A手下的业务员A1，在第1个月放款金额为10万。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 问题一：每个老板手下，那个业务员放款金额最多？\n",
    "解决方法：\n",
    "- 按照owner分组，对fk_money降序排列，取第一个数据\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "result1_df = df.sort_values(by=\"fk_money\",ascending=False).groupby(by=\"owner\").head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>boss</th>\n",
       "      <th>owner</th>\n",
       "      <th>month</th>\n",
       "      <th>fk_money</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>2</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>B1</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A2</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  boss owner  month  fk_money\n",
       "9    C    C2      2       100\n",
       "7    C    C1      2        80\n",
       "5    B    B2      2        60\n",
       "3    B    B1      1        40\n",
       "2    A    A2      1        30\n",
       "1    A    A1      2        20"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 拓展：如何取第二大的数据呢？\n",
    "- GroupBy.nth()，取每一组第n行的数据，n从0开始。0代表第一行。\n",
    "- 没有第n行的时候，不取。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "result1_df = df.sort_values(by=\"fk_money\",ascending=False).groupby(by=\"owner\").nth(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>boss</th>\n",
       "      <th>month</th>\n",
       "      <th>fk_money</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>owner</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A1</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B2</th>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C1</th>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C2</th>\n",
       "      <td>C</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      boss  month  fk_money\n",
       "owner                      \n",
       "A1       A      1        10\n",
       "B2       B      1        50\n",
       "C1       C      1        70\n",
       "C2       C      1        90"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 问题二：业务员每个月的放款金额占比情况？\n",
    "解决方案：\n",
    "1. 计算出每个业务员总的放款金额owner_total_fk_money\n",
    "2. 将df与计算好的owner_total_fk_money合并\n",
    "3. fk_money除以owner_total_fk_money得到需要的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 代码实现：\n",
    "owner_total_fk_money = df.groupby(by=\"owner\",as_index=False).agg({\"fk_money\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "result1_df = pd.merge(df,owner_total_fk_money,on=\"owner\",how=\"left\",suffixes=(\"\",\"_total\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "result1_df[\"rate\"] = (result1_df[\"fk_money\"]/result1_df[\"fk_money_total\"]).map(lambda x:\"{:.2%}\".format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>boss</th>\n",
       "      <th>owner</th>\n",
       "      <th>month</th>\n",
       "      <th>fk_money</th>\n",
       "      <th>fk_money_total</th>\n",
       "      <th>rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>33.33%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>30</td>\n",
       "      <td>66.67%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>A2</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>100.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>B1</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>40</td>\n",
       "      <td>100.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>110</td>\n",
       "      <td>45.45%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "      <td>110</td>\n",
       "      <td>54.55%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>1</td>\n",
       "      <td>70</td>\n",
       "      <td>150</td>\n",
       "      <td>46.67%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>2</td>\n",
       "      <td>80</td>\n",
       "      <td>150</td>\n",
       "      <td>53.33%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>190</td>\n",
       "      <td>47.37%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "      <td>190</td>\n",
       "      <td>52.63%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  boss owner  month  fk_money  fk_money_total     rate\n",
       "0    A    A1      1        10              30   33.33%\n",
       "1    A    A1      2        20              30   66.67%\n",
       "2    A    A2      1        30              30  100.00%\n",
       "3    B    B1      1        40              40  100.00%\n",
       "4    B    B2      1        50             110   45.45%\n",
       "5    B    B2      2        60             110   54.55%\n",
       "6    C    C1      1        70             150   46.67%\n",
       "7    C    C1      2        80             150   53.33%\n",
       "8    C    C2      1        90             190   47.37%\n",
       "9    C    C2      2       100             190   52.63%"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result1_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 结果解释：\n",
    "A1业务员在第一个月放款10万，占其总放款（30万）比例为33.33%，第二个月放款20万，占比为66.67%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 问题三：每个老板手下业务员放款占比？\n",
    "解决思路：\n",
    "1. 需要知道每个老板总的放款金额，boss_df\n",
    "2. 需要知道每个业务员的放款金额，owner_df\n",
    "3. 马昭boss字段合并boss_df和owner_df\n",
    "4. 业务员的放款金额除以每个老板总的放款金额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算每一个boss的总fk_money\n",
    "boss_df = df.groupby(by=\"boss\",as_index=False).agg({\"fk_money\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算每一个owner的总fk_money\n",
    "owner_df = df.groupby(by=[\"boss\",\"owner\"],as_index=False).agg({\"fk_money\":\"sum\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并owner_df和boss_df\n",
    "result2_df = pd.merge(owner_df,boss_df,on=\"boss\",how=\"left\",suffixes=(\"_owner\",\"_boss\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "result2_df[\"占比\"] = (result_df[\"fk_money_owner\"]/result_df[\"fk_money_boss\"]).map(lambda x:\"{:.2%}\".format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>boss</th>\n",
       "      <th>owner</th>\n",
       "      <th>fk_money_owner</th>\n",
       "      <th>fk_money_boss</th>\n",
       "      <th>占比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>A1</td>\n",
       "      <td>30</td>\n",
       "      <td>60</td>\n",
       "      <td>50.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>A2</td>\n",
       "      <td>30</td>\n",
       "      <td>60</td>\n",
       "      <td>50.00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B</td>\n",
       "      <td>B1</td>\n",
       "      <td>40</td>\n",
       "      <td>150</td>\n",
       "      <td>26.67%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>B2</td>\n",
       "      <td>110</td>\n",
       "      <td>150</td>\n",
       "      <td>73.33%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>C</td>\n",
       "      <td>C1</td>\n",
       "      <td>150</td>\n",
       "      <td>340</td>\n",
       "      <td>44.12%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>C</td>\n",
       "      <td>C2</td>\n",
       "      <td>190</td>\n",
       "      <td>340</td>\n",
       "      <td>55.88%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  boss owner  fk_money_owner  fk_money_boss      占比\n",
       "0    A    A1              30             60  50.00%\n",
       "1    A    A2              30             60  50.00%\n",
       "2    B    B1              40            150  26.67%\n",
       "3    B    B2             110            150  73.33%\n",
       "4    C    C1             150            340  44.12%\n",
       "5    C    C2             190            340  55.88%"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result2_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
